﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using Oracle.DataAccess.Client;

namespace ISHotelApp.Database
{
    [Serializable]
    public class PobytTable : DbTable
    {
        public static String TABLE_NAME = "Pobyt";

        public String SQL_SELECT = "SELECT * FROM Pobyt ORDER BY id_pobytu DESC";
        public String SQL_SELECT_id_pobytu = "SELECT * FROM Pobyt WHERE id_pobytu=:id_pobytu";
        public String SQL_SELECT_pokojHistory = "SELECT p.id_pobytu, p.prijezd, p.odjezd, h.jmeno|| ' ' ||h.prijmeni FROM Pobyt p, Objednavka_pokoje o, Seznam_hostu s, Host h WHERE o.id_pobytu = p.id_pobytu AND s.id_pobytu = p.id_pobytu AND s.rodne_cislo = h.rodne_cislo AND o.id_pokoje=:id_pokoje";
        public String SQL_UPDATE = "UPDATE Pobyt SET id_taxy=:id_taxy, prijezd=:prijezd, odjezd=:odjezd WHERE id_pobytu=:id_pobytu";
        public String SQL_UPDATE_zaplaceno = "UPDATE Pobyt SET zaplaceno=:zaplaceno WHERE id_pobytu=:id_pobytu";

        public PobytTable()
            : base(TABLE_NAME)
        {
        }

        /**
 * Insert the record.
 **/
        public int Insert(Pobyt Pobyt)
        {

            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand("projekt_vloz_pobyt");
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_taxy].Value = Pobyt.id_taxy;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_prijezd, OracleDbType.Date));
            command.Parameters[":" + Pobyt.ATTR_prijezd].Value = Pobyt.prijezd;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_odjezd, OracleDbType.Date));
            command.Parameters[":" + Pobyt.ATTR_odjezd].Value = Pobyt.odjezd;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pokoje, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pokoje].Value = Pobyt.id_pokoje;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_rodne_cislo, OracleDbType.Varchar2, Pobyt.ATTR_rodne_cislo.Length));
            command.Parameters[":" + Pobyt.ATTR_rodne_cislo].Value = Pobyt.rodne_cislo;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_stravovani, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_stravovani].Value = Pobyt.id_stravovani;

            int ret;

            mDatabase.BeginTransaction();

            try
            {
                ret = mDatabase.Insert(command);
                mDatabase.EndTransaction();
            }
            catch (Exception e)
            {
                mDatabase.Rollback();
                throw e;
                
            }

            mDatabase.Close();
            return ret;
        }

        /**
         * Update the record.
         **/
        public int Update(Pobyt Pobyt)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_UPDATE);
            
            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_taxy].Value = Pobyt.id_taxy;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_prijezd, OracleDbType.Date));
            command.Parameters[":" + Pobyt.ATTR_prijezd].Value = Pobyt.prijezd;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_odjezd, OracleDbType.Date));
            command.Parameters[":" + Pobyt.ATTR_odjezd].Value = Pobyt.odjezd;
            
            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pobytu, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pobytu].Value = Pobyt.id_pobytu;

            int ret = mDatabase.Update(command);
            mDatabase.Close();
            return ret;
        }

        public int UpdateZaplaceno(int id_pobytu, int zaplaceno)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_UPDATE_zaplaceno);

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_zaplaceno, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_zaplaceno].Value = zaplaceno;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pobytu, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pobytu].Value = id_pobytu;

            int ret = mDatabase.Update(command);
            mDatabase.Close();
            return ret;
        }

        public int Prepocitat(int id_pobytu)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand("projekt_spocti_cenu");
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pobytu, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pobytu].Value = id_pobytu;

            int ret = 0;

            mDatabase.BeginTransaction();

            try
            {
                ret = mDatabase.Insert(command);
                mDatabase.EndTransaction();
            }
            catch (Exception e)
            {
                mDatabase.Rollback();
            }

            mDatabase.Close();
            return ret;
        }

        /**
         * Select records.
         **/
        public Collection<Pobyt> Select()
        {
            Database mDatabase = new Database();
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT);
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Pobyt> Pobyts = Read(reader);
            reader.Close();
            mDatabase.Close();
            return Pobyts;
        }

        public Collection<Pobyt> SelectPokojHistory(int id_pokoje)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT_pokojHistory);
            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pokoje, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pokoje].Value = id_pokoje;
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Pobyt> Pobyts = new Collection<Pobyt>( );// = Read(reader);

            while (reader.Read())
            {
                Pobyt Pobyt1 = new Pobyt();
                Pobyt1.id_pobytu = (int)reader.GetDecimal(0);
                Pobyt1.prijezd = reader.GetDateTime(1);
                Pobyt1.odjezd = reader.GetDateTime(2);
                Pobyt1.jmeno = reader.GetString(3);
                Pobyts.Add(Pobyt1);
            }


            reader.Close();
            mDatabase.Close();
            return Pobyts;
        }


        /**
         * Select the record.
         **/
        public Pobyt Select(int id_pobytu)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT_id_pobytu);

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pobytu, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pobytu].Value = id_pobytu;
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Pobyt> Pobyts = Read(reader);
            Pobyt Pobyt1 = null;
            if (Pobyts.Count == 1)
            {
                Pobyt1 = Pobyts[0];
            }
            reader.Close();
            mDatabase.Close();
            return Pobyt1;
        }

        private Collection<Pobyt> Read(OracleDataReader reader)
        {
            Collection<Pobyt> Pobyts = new Collection<Pobyt>();

            while (reader.Read())
            {
                Pobyt Pobyt = new Pobyt();
                Pobyt.id_pobytu = (int)reader.GetDecimal(0);
                Pobyt.id_taxy = (int)reader.GetDecimal(1);
                Pobyt.prijezd = reader.GetDateTime(2);
                Pobyt.odjezd = reader.GetDateTime(3);
                Pobyt.celkova_suma = (int)reader.GetDecimal(4);
                Pobyt.zaplaceno = (int)reader.GetDecimal(5);
                Pobyts.Add(Pobyt);
            }
            return Pobyts;
        }

        /**
         * Delete the record.
         */
        public int Delete(int id_pobytu)
        {
            mDatabase.Connect();
            //OracleCommand command = mDatabase.CreateCommand(SQL_DELETE);
            OracleCommand command = mDatabase.CreateCommand("projekt_smaz_pobyt");
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new OracleParameter(":" + Pobyt.ATTR_id_pobytu, OracleDbType.Decimal));
            command.Parameters[":" + Pobyt.ATTR_id_pobytu].Value = id_pobytu;
            int ret;
            
            mDatabase.BeginTransaction();
            try
            {
                ret = mDatabase.Delete(command);
                mDatabase.EndTransaction();
            }
            catch (Exception e)
            {
                mDatabase.Rollback();
                throw e;
            }
            mDatabase.Close();
            return ret;
        }
    }
}